package com.app.business.service;

import com.app.business.model.Contract;
import com.app.system.jpa.JpaBaseDao;
import com.app.system.utils.CollectionUtils;
import com.app.system.utils.dataType.StringUtils;
import com.google.common.collect.Maps;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.Query;
import java.util.List;
import java.util.Map;

/**
 * 统计查询专用根据年统计
 * Created by DDZ on 2020/6/15.
 */
@SuppressWarnings(value = "ALL")
@Service
public class StatisticsForYearService extends JpaBaseDao<Contract, Integer> {
    @Autowired
    private HouseService houseService;
    @Autowired
    private AzProjectService azProjectService;
    @Autowired
    private ZsProjectService zsProjectService;


    /**
     * 根据选择房源时间得到年份
     *
     * @return
     */
    public List<String> getChooseHouseYearList(String town) {

        StringBuffer sql = new StringBuffer(" select DISTINCT(DATE_FORMAT(h.choose_house_date,'%Y')) AS 'year'");
        sql.append(" FROM house h INNER JOIN contract c ON h.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON h.az_project_id=azp.id");
        sql.append(" WHERE  h.house_Status=2  ");
        if (town != null && !town.equals("")) {
            sql.append(" AND azp.town=:town ");
        }
        sql.append(" ORDER BY DATE_FORMAT(h.choose_house_date,'%Y') DESC");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        if (town != null && !town.equals("")) {
            query.setParameter("town", town);
        }
        List<String> list = (List<String>) query.getResultList();
        return list;
    }

    /**
     * 根据已选房源得到协议id list
     *
     * @return
     */
    public List<Integer> getContractIdByHouseContract() {

        StringBuffer sql = new StringBuffer("  select h.contract_id FROM house h INNER JOIN contract c ON h.contract_id=c.id  ");
        sql.append(" INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON h.az_project_id=azp.id ");
        sql.append(" where h.house_Status=2");
        return this.findSqlObject(sql.toString(), null);
    }

    /**
     * 根据乡镇得到房源统计，通过年份，乡镇
     *
     * @param townStr
     * @return
     */
    public Map<String, Map<String, Map>> getHouseStaticsForYear(String townStr) {

        StringBuffer sql = new StringBuffer("SELECT azp.town as town ,");
        sql.append(" DATE_FORMAT(h.choose_house_date,'%Y') AS 'year',");
        sql.append(" COUNT(DISTINCT(zsp.id)) AS 'zspCount',");
        sql.append(" COUNT(DISTINCT(c.id)) AS 'contractCount',");
        sql.append(" SUM(CASE WHEN h.house_type = '02' AND h.house_hold = '0100' THEN 1 ELSE 0 END) AS smallHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '02' AND h.house_hold = '0100' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS smallHouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0102' THEN 1 ELSE 0 END) AS 0102HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0102' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0102HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0201' THEN 1 ELSE 0 END) AS 0201HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0201' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0201HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0202' THEN 1 ELSE 0 END) AS 0202HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0202' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0202HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0302' THEN 1 ELSE 0 END) AS 0302HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0302' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0302HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0402' THEN 1 ELSE 0 END) AS 0402HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0402' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0402HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0502' THEN 1 ELSE 0 END) AS 0502HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0502' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0502HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN 1 ELSE 0 END) AS normalHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS normalHouseArea,");

        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN 1 ELSE 0 END) AS fsHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS fsHouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_Status = 2 THEN 1 ELSE 0 END) AS houseNum,");
        sql.append(" SUM(CASE WHEN h.house_Status = 2 THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS houseArea");

        sql.append(" FROM house h INNER JOIN contract c ON h.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON h.az_project_id=azp.id");
        sql.append(" WHERE  h.house_Status=2 and c.choose_house_status=2 ");
        if (townStr != null && !townStr.equals("")) {
            sql.append(" and azp.town= " + townStr + " ");
        }
        sql.append(" GROUP BY DATE_FORMAT(h.choose_house_date,'%Y'),azp.town");
        sql.append(" ORDER BY DATE_FORMAT(h.choose_house_date,'%Y') DESC,azp.town");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map>> map = Maps.newHashMap();
        Map<String, Map> itemMap = Maps.newHashMap();
        Map itemItemMap = null;
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map item : list) {
                String year = item.get("year").toString();
                String town = item.get("town").toString();
                itemMap = map.get(year) == null ? Maps.<String, Map>newHashMap() : map.get(year);
                itemItemMap = itemMap.get(town) == null ? Maps.newHashMap() : itemMap.get(town);
                itemMap.put(town, item);
                map.put(year, itemMap);
            }
        }
        return map;

    }

    /**
     * 根据乡镇得到储物间统计，通过年份，乡镇
     *
     * @param townStr
     * @return
     */
    public Map<String, Map<String, Map>> getStorageRoomStaticsForYear(String townStr) {

        StringBuffer sql = new StringBuffer("SELECT azp.town as town ,");
        sql.append(" DATE_FORMAT(s.choose_house_date,'%Y') AS 'year',");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN 1 ELSE 0 END) AS storageRoomNum,");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN (IFNULL(s.build_Area,0)) ELSE 0 END) AS storageRoomArea");

        sql.append(" FROM Storage_Room s INNER JOIN contract c ON s.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON s.az_project_id=azp.id");
        sql.append(" WHERE  s.house_Status=2 and c.choose_house_status=2 ");
        if (townStr != null && !townStr.equals("")) {
            sql.append(" and azp.town= " + townStr + " ");
        }
        sql.append(" GROUP BY DATE_FORMAT(s.choose_house_date,'%Y'),azp.town");
        sql.append(" ORDER BY DATE_FORMAT(s.choose_house_date,'%Y') DESC,azp.town");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map>> map = Maps.newHashMap();
        Map<String, Map> itemMap = Maps.newHashMap();
        Map itemItemMap = null;
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map item : list) {
                String year = item.get("year").toString();
                String town = item.get("town").toString();
                itemMap = map.get(year) == null ? Maps.<String, Map>newHashMap() : map.get(year);
                itemItemMap = itemMap.get(town) == null ? Maps.newHashMap() : itemMap.get(town);
                itemMap.put(town, item);
                map.put(year, itemMap);
            }
        }
        return map;

    }

    /**
     * 根据乡镇得到店铺统计，通过年份，乡镇
     *
     * @param townStr
     * @return
     */
    public Map<String, Map<String, Map>> getShopHouseStaticsForYear(String townStr) {

        StringBuffer sql = new StringBuffer("SELECT azp.town as town ,");
        sql.append(" DATE_FORMAT(s.choose_house_date,'%Y') AS 'year',");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN 1 ELSE 0 END) AS shopHouseNum,");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN (IFNULL(s.build_Area,0)) ELSE 0 END) AS shopHouseArea");

        sql.append(" FROM shop_House s INNER JOIN contract c ON s.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON s.az_project_id=azp.id");
        sql.append(" WHERE  s.house_Status=2 and c.choose_house_status=2 ");
        if (townStr != null && !townStr.equals("")) {
            sql.append(" and azp.town= " + townStr + " ");
        }
        sql.append(" GROUP BY DATE_FORMAT(s.choose_house_date,'%Y'),azp.town");
        sql.append(" ORDER BY DATE_FORMAT(s.choose_house_date,'%Y') DESC,azp.town");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map>> map = Maps.newHashMap();
        Map<String, Map> itemMap = Maps.newHashMap();
        Map itemItemMap = null;
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map item : list) {
                String year = item.get("year").toString();
                String town = item.get("town").toString();
                itemMap = map.get(year) == null ? Maps.<String, Map>newHashMap() : map.get(year);
                itemItemMap = itemMap.get(town) == null ? Maps.newHashMap() : itemMap.get(town);
                itemMap.put(town, item);
                map.put(year, itemMap);
            }
        }
        return map;

    }

    /**
     * 根据乡镇得到车位统计，通过年份，乡镇
     *
     * @param townStr
     * @return
     */
    public Map<String, Map<String, Map>> getCarLotStaticsForYear(String townStr) {

        StringBuffer sql = new StringBuffer("SELECT azp.town as town ,");
        sql.append(" DATE_FORMAT(cl.choose_house_date,'%Y') AS 'year',");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  THEN 1 ELSE 0 END) AS carLotNum,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (1,2,3,7,9,10,11,12,13) THEN 1 ELSE 0 END) AS ptCarLotNum,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (4,5,6,8) THEN 1 ELSE 0 END) AS rfCarLotNum,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  THEN (IFNULL(cl.build_Area,0)) ELSE 0 END) AS carLotArea,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (1,2,3,7,9,10,11,12,13) THEN (IFNULL(cl.build_Area,0)) ELSE 0 END) AS ptCarLotArea,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (4,5,6,8) THEN (IFNULL(cl.build_Area,0)) ELSE 0 END) AS rfCarLotArea");

        sql.append(" FROM car_lot cl INNER JOIN contract c ON cl.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON cl.az_project_id=azp.id");
        sql.append(" WHERE  cl.house_Status=2 and c.choose_house_status=2 ");
        if (townStr != null && !townStr.equals("")) {
            sql.append(" and azp.town= " + townStr + " ");
        }
        sql.append(" GROUP BY DATE_FORMAT(cl.choose_house_date,'%Y'),azp.town");
        sql.append(" ORDER BY DATE_FORMAT(cl.choose_house_date,'%Y') DESC,azp.town");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map>> map = Maps.newHashMap();
        Map<String, Map> itemMap = Maps.newHashMap();
        Map itemItemMap = null;
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map item : list) {
                String year = item.get("year").toString();
                String town = item.get("town").toString();
                itemMap = map.get(year) == null ? Maps.<String, Map>newHashMap() : map.get(year);
                itemItemMap = itemMap.get(town) == null ? Maps.newHashMap() : itemMap.get(town);
                itemMap.put(town, item);
                map.put(year, itemMap);
            }
        }
        return map;

    }


    /**
     * 某一年某乡镇各个安置区情况
     * 根据安置区得到房源统计，通过年份，乡镇
     *
     * @param townStr
     * @return
     */
    public Map<String, Map<String, Map>> getHouseStaticsForYearAndTown(String townStr, String yearStr) {

        StringBuffer sql = new StringBuffer("SELECT azp.town as town ,azp.id as azpId ,azp.project_Name as azProjectName, ");
        sql.append(" DATE_FORMAT(h.choose_house_date,'%Y') AS 'year',");
        sql.append(" COUNT(DISTINCT(c.id)) AS 'contractCount',");
        sql.append(" COUNT(DISTINCT(zsp.id)) AS 'zspCount',");
        sql.append(" SUM(CASE WHEN h.house_type = '02' AND h.house_hold = '0100' THEN 1 ELSE 0 END) AS smallHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '02' AND h.house_hold = '0100' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS smallHouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0102' THEN 1 ELSE 0 END) AS 0102HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0102' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0102HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0201' THEN 1 ELSE 0 END) AS 0201HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0201' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0201HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0202' THEN 1 ELSE 0 END) AS 0202HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0202' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0202HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0302' THEN 1 ELSE 0 END) AS 0302HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0302' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0302HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0402' THEN 1 ELSE 0 END) AS 0402HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0402' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0402HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0502' THEN 1 ELSE 0 END) AS 0502HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0502' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0502HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN 1 ELSE 0 END) AS normalHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS normalHouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN 1 ELSE 0 END) AS fsHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS fsHouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_Status = 2 THEN 1 ELSE 0 END) AS houseNum,");
        sql.append(" SUM(CASE WHEN h.house_Status = 2 THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS houseArea");

        sql.append(" FROM house h INNER JOIN contract c ON h.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON h.az_project_id=azp.id");
        sql.append(" WHERE  h.house_Status=2 and c.choose_house_status=2 ");
        if (!townStr.isEmpty()) {
            sql.append(" and azp.town= " + townStr + " ");
        }
        if (!yearStr.isEmpty()) {
            sql.append(" and DATE_FORMAT(h.choose_house_date,'%Y') = " + yearStr + "");
        }
        sql.append(" GROUP BY DATE_FORMAT(h.choose_house_date,'%Y'),azp.id");
        sql.append(" ORDER BY DATE_FORMAT(h.choose_house_date,'%Y') DESC,azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map>> map = Maps.newHashMap();
        Map<String, Map> itemMap = Maps.newHashMap();
        Map itemItemMap = null;
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map item : list) {
                String year = item.get("year").toString();
                String azProjectId = item.get("azpId").toString();
                itemMap = map.get(year) == null ? Maps.<String, Map>newHashMap() : map.get(year);
                itemItemMap = itemMap.get(azProjectId) == null ? Maps.newHashMap() : itemMap.get(azProjectId);
                itemMap.put(azProjectId, item);
                map.put(year, itemMap);
            }
        }
        return map;

    }

    /**
     * 根据乡镇、年度、安置项目
     * 获取所有征收项目选房情况
     *
     * @param townStr
     * @return
     */
    public Map<String, Map> getHouseStaticsForYearAndTownAndAzpIdGroupByZsp(String townCode, String yearStr, Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id as zsProjectId ,zsp.start_Year as startYear,zsp.project_Name as zsProjectName, ");
        sql.append(" DATE_FORMAT(h.choose_house_date,'%Y') AS 'year',");
        sql.append(" COUNT(DISTINCT(c.id)) AS 'contractCount',");
        sql.append(" SUM(CASE WHEN h.house_type = '02' AND h.house_hold = '0100' THEN 1 ELSE 0 END) AS smallHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '02' AND h.house_hold = '0100' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS smallHouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0102' THEN 1 ELSE 0 END) AS 0102HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0102' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0102HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0201' THEN 1 ELSE 0 END) AS 0201HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0201' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0201HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0202' THEN 1 ELSE 0 END) AS 0202HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0202' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0202HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0302' THEN 1 ELSE 0 END) AS 0302HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0302' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0302HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0402' THEN 1 ELSE 0 END) AS 0402HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0402' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0402HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0502' THEN 1 ELSE 0 END) AS 0502HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' AND h.house_hold = '0502' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0502HouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN 1 ELSE 0 END) AS normalHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS normalHouseArea,");
        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN 1 ELSE 0 END) AS fsHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS fsHouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_Status = 2 THEN 1 ELSE 0 END) AS houseNum,");
        sql.append(" SUM(CASE WHEN h.house_Status = 2 THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS houseArea");

        sql.append(" FROM house h INNER JOIN contract c ON h.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON h.az_project_id=azp.id");
        sql.append(" WHERE  h.house_Status=2 and c.choose_house_status=2 ");
        if (StringUtils.isNotEmpty(townCode)) {
            sql.append(" and azp.town= " + townCode + " ");
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            sql.append(" and DATE_FORMAT(h.choose_house_date,'%Y') = " + yearStr + "");
        }
        if (azProjectId != null & azProjectId != 0) {
            sql.append(" and azp.id= " + azProjectId);
        }
        sql.append(" GROUP BY zsProjectId");
        sql.append(" ORDER BY startYear desc,zsProjectId");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> itemMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map map : list) {
                String zsProjectId = map.get("zsProjectId").toString();
                itemMap.put(zsProjectId, map);
            }
        }
        return itemMap;

    }


    /**
     * 某一年某乡镇各个安置区情况
     * 根据乡镇得到储物间统计，通过年份，乡镇
     *
     * @param townStr
     * @return
     */
    public Map<String, Map<String, Map>> getStorageRoomStaticsForYearAndTown(String townStr, String yearStr) {

        StringBuffer sql = new StringBuffer("SELECT azp.town as town ,azp.id as azpId,azp.project_Name as azProjectName,  ");
        sql.append(" DATE_FORMAT(s.choose_house_date,'%Y') AS 'year',");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN 1 ELSE 0 END) AS storageRoomNum,");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN (IFNULL(s.build_Area,0)) ELSE 0 END) AS storageRoomArea");

        sql.append(" FROM Storage_Room s INNER JOIN contract c ON s.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON s.az_project_id=azp.id");
        sql.append(" WHERE  s.house_Status=2 and c.choose_house_status=2 ");
        if (!townStr.isEmpty()) {
            sql.append(" and azp.town= " + townStr + " ");
        }
        if (!yearStr.isEmpty()) {
            sql.append(" and DATE_FORMAT(s.choose_house_date,'%Y') = " + yearStr + "");
        }
        sql.append(" GROUP BY DATE_FORMAT(s.choose_house_date,'%Y'),azp.id");
        sql.append(" ORDER BY DATE_FORMAT(s.choose_house_date,'%Y') DESC,azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map>> map = Maps.newHashMap();
        Map<String, Map> itemMap = Maps.newHashMap();
        Map itemItemMap = null;
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map item : list) {
                String year = item.get("year").toString();
                String azProjectId = item.get("azpId").toString();
                itemMap = map.get(year) == null ? Maps.<String, Map>newHashMap() : map.get(year);
                itemItemMap = itemMap.get(azProjectId) == null ? Maps.newHashMap() : itemMap.get(azProjectId);
                itemMap.put(azProjectId, item);
                map.put(year, itemMap);
            }
        }
        return map;

    }

    /**
     * 某一年某乡镇各个安置区情况
     * 根据乡镇得到店铺统计，通过年份，乡镇
     *
     * @param townStr
     * @return
     */
    public Map<String, Map<String, Map>> getShopHouseStaticsForYearAndTown(String townStr, String yearStr) {

        StringBuffer sql = new StringBuffer("SELECT azp.town as town ,azp.id as azpId,azp.project_Name as azProjectName,  ");
        sql.append(" DATE_FORMAT(s.choose_house_date,'%Y') AS 'year',");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN 1 ELSE 0 END) AS shopHouseNum,");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN (IFNULL(s.build_Area,0)) ELSE 0 END) AS shopHouseArea");

        sql.append(" FROM shop_house s INNER JOIN contract c ON s.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON s.az_project_id=azp.id");
        sql.append(" WHERE  s.house_Status=2 and c.choose_house_status=2 ");
        if (!townStr.isEmpty()) {
            sql.append(" and azp.town= " + townStr + " ");
        }
        if (!yearStr.isEmpty()) {
            sql.append(" and DATE_FORMAT(s.choose_house_date,'%Y') = " + yearStr + "");
        }
        sql.append(" GROUP BY DATE_FORMAT(s.choose_house_date,'%Y'),azp.id");
        sql.append(" ORDER BY DATE_FORMAT(s.choose_house_date,'%Y') DESC,azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map>> map = Maps.newHashMap();
        Map<String, Map> itemMap = Maps.newHashMap();
        Map itemItemMap = null;
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map item : list) {
                String year = item.get("year").toString();
                String azProjectId = item.get("azpId").toString();
                itemMap = map.get(year) == null ? Maps.<String, Map>newHashMap() : map.get(year);
                itemItemMap = itemMap.get(azProjectId) == null ? Maps.newHashMap() : itemMap.get(azProjectId);
                itemMap.put(azProjectId, item);
                map.put(year, itemMap);
            }
        }
        return map;

    }


    /**
     * 根据乡镇、年度、安置项目
     * 获取所有征收项目选房储物间情况
     *
     * @param townStr
     * @return
     */
    public Map<String, Map> getStorageRoomStaticsorYearAndTownAndAzpIdGroupByZsp(String townCode, String yearStr, Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id as zsProjectId,zsp.start_Year as startYear,zsp.project_Name as zsProjectName,  ");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN 1 ELSE 0 END) AS storageRoomNum,");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN (IFNULL(s.build_Area,0)) ELSE 0 END) AS storageRoomArea");

        sql.append(" FROM Storage_Room s INNER JOIN contract c ON s.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON s.az_project_id=azp.id");
        sql.append(" WHERE  s.house_Status=2 and c.choose_house_status=2 ");
        if (StringUtils.isNotEmpty(townCode)) {
            sql.append(" and azp.town= " + townCode + " ");
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            sql.append(" and DATE_FORMAT(s.choose_house_date,'%Y') = " + yearStr + "");
        }
        if (azProjectId != null & azProjectId != 0) {
            sql.append(" and azp.id= " + azProjectId);
        }
        sql.append(" GROUP BY zsProjectId");
        sql.append(" ORDER BY startYear desc,zsProjectId");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> itemMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map map : list) {
                String zsProjectId = map.get("zsProjectId").toString();
                itemMap.put(zsProjectId, map);
            }
        }
        return itemMap;
    }

    /**
     * 根据乡镇、年度、安置项目
     * 获取所有征收项目选房储物间情况
     *
     * @param townStr
     * @return
     */
    public Map<String, Map> getShopHouseStaticsorYearAndTownAndAzpIdGroupByZsp(String townCode, String yearStr, Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id as zsProjectId,zsp.start_Year as startYear,zsp.project_Name as zsProjectName,  ");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN 1 ELSE 0 END) AS shopHouseNum,");
        sql.append(" SUM(CASE WHEN s.house_Status = 2  THEN (IFNULL(s.build_Area,0)) ELSE 0 END) AS shopHouseArea");

        sql.append(" FROM shop_house s INNER JOIN contract c ON s.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON s.az_project_id=azp.id");
        sql.append(" WHERE  s.house_Status=2 and c.choose_house_status=2 ");
        if (StringUtils.isNotEmpty(townCode)) {
            sql.append(" and azp.town= " + townCode + " ");
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            sql.append(" and DATE_FORMAT(s.choose_house_date,'%Y') = " + yearStr + "");
        }
        if (azProjectId != null & azProjectId != 0) {
            sql.append(" and azp.id= " + azProjectId);
        }
        sql.append(" GROUP BY zsProjectId");
        sql.append(" ORDER BY startYear desc,zsProjectId");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> itemMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map map : list) {
                String zsProjectId = map.get("zsProjectId").toString();
                itemMap.put(zsProjectId, map);
            }
        }
        return itemMap;
    }

    /**
     * 某一年某乡镇各个安置区情况
     * 根据乡镇得到车位统计，通过年份，乡镇
     *
     * @param townStr
     * @return
     */
    public Map<String, Map<String, Map>> getCarLotStaticsForYearAndTown(String townStr, String yearStr) {

        StringBuffer sql = new StringBuffer("SELECT azp.town as town ,azp.id as azpId,azp.project_Name as azProjectName, ");
        sql.append(" DATE_FORMAT(cl.choose_house_date,'%Y') AS 'year',");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  THEN 1 ELSE 0 END) AS carLotNum,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (1,2,3,7,9,10,11,12,13) THEN 1 ELSE 0 END) AS ptCarLotNum,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (4,5,6,8) THEN 1 ELSE 0 END) AS rfCarLotNum,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  THEN (IFNULL(cl.build_Area,0)) ELSE 0 END) AS carLotArea,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (1,2,3,7,9,10,11,12,13) THEN (IFNULL(cl.build_Area,0)) ELSE 0 END) AS ptCarLotArea,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (4,5,6,8) THEN (IFNULL(cl.build_Area,0)) ELSE 0 END) AS rfCarLotArea");

        sql.append(" FROM car_lot cl INNER JOIN contract c ON cl.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON cl.az_project_id=azp.id");
        sql.append(" WHERE  cl.house_Status=2 and c.choose_house_status=2 ");
        if (!townStr.isEmpty()) {
            sql.append(" and azp.town= " + townStr + " ");
        }
        if (!yearStr.isEmpty()) {
            sql.append(" and DATE_FORMAT(cl.choose_house_date,'%Y') = " + yearStr + "");
        }
        sql.append(" GROUP BY DATE_FORMAT(cl.choose_house_date,'%Y'),azp.id");
        sql.append(" ORDER BY DATE_FORMAT(cl.choose_house_date,'%Y') DESC,azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map>> map = Maps.newHashMap();
        Map<String, Map> itemMap = Maps.newHashMap();
        Map itemItemMap = null;
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map item : list) {
                String year = item.get("year").toString();
                String azProjectId = item.get("azpId").toString();
                itemMap = map.get(year) == null ? Maps.<String, Map>newHashMap() : map.get(year);
                itemItemMap = itemMap.get(azProjectId) == null ? Maps.newHashMap() : itemMap.get(azProjectId);
                itemMap.put(azProjectId, item);
                map.put(year, itemMap);
            }
        }
        return map;

    }

    /**
     * 根据乡镇、年度、安置项目
     * 获取所有征收项目选房车位情况
     *
     * @param townStr
     * @return
     */
    public Map<String, Map> getCarLotStaticsForYearAndTownAndAzpIdGroupByZsp(String townCode, String yearStr, Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id as zsProjectId,zsp.start_Year as startYear,zsp.project_Name as zsProjectName, ");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  THEN 1 ELSE 0 END) AS carLotNum,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (1,2,3,7,9,10,11,12,13) THEN 1 ELSE 0 END) AS ptCarLotNum,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (4,5,6,8) THEN 1 ELSE 0 END) AS rfCarLotNum,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  THEN (IFNULL(cl.build_Area,0)) ELSE 0 END) AS carLotArea,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (1,2,3,7,9,10,11,12,13) THEN (IFNULL(cl.build_Area,0)) ELSE 0 END) AS ptCarLotArea,");
        sql.append(" SUM(CASE WHEN cl.house_Status = 2  AND cl.car_lot_type in (4,5,6,8) THEN (IFNULL(cl.build_Area,0)) ELSE 0 END) AS rfCarLotArea");

        sql.append(" FROM car_lot cl INNER JOIN contract c ON cl.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id =zsp.id INNER JOIN az_project azp ON cl.az_project_id=azp.id");
        sql.append(" WHERE  cl.house_Status=2 and c.choose_house_status=2 ");
        if (StringUtils.isNotEmpty(townCode)) {
            sql.append(" and azp.town= " + townCode + " ");
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            sql.append(" and DATE_FORMAT(cl.choose_house_date,'%Y') = " + yearStr + "");
        }
        if (azProjectId != null & azProjectId != 0) {
            sql.append(" and azp.id= " + azProjectId);
        }
        sql.append(" GROUP BY zsProjectId");
        sql.append(" ORDER BY startYear desc,zsProjectId");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> list = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> itemMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map map : list) {
                String zsProjectId = map.get("zsProjectId").toString();
                itemMap.put(zsProjectId, map);
            }
        }
        return itemMap;
    }

}
